﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLACQUY.DTO;
using System.Data.OleDb;
using System.Collections;

namespace QLACQUY.DAO
{
    class SanPhamDAO:DataAccess
    {
        public int Them(SanPhamDTO dto)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "insert into SAN_PHAM(TenSP, QuyCachSP, GiaSi, GiaLe, MaNSX, MoTa, HinhAnh, TrangThai) values(?,?,?,?,?,?,?, 1)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenSP", OleDbType.WChar);
            cmd.Parameters.Add("@QuyCachSP", OleDbType.WChar);
            cmd.Parameters.Add("@GiaSi", OleDbType.Numeric);
            cmd.Parameters.Add("@GiaLe", OleDbType.Numeric);
            cmd.Parameters.Add("@MaNSX", OleDbType.Integer);
            cmd.Parameters.Add("@MoTa", OleDbType.WChar);
            cmd.Parameters.Add("@HinhAnh", OleDbType.WChar);

            cmd.Parameters["@TenSP"].Value = dto.TenSanPham;
            cmd.Parameters["@QuyCachSP"].Value = dto.QuyCach;
            cmd.Parameters["@GiaSi"].Value = dto.DonGiaSy;
            cmd.Parameters["@GiaLe"].Value = dto.DonGiaLe;
            cmd.Parameters["@MaNSX"].Value = dto.MaNSX;
            cmd.Parameters["@MoTa"].Value = dto.MoTa;
            cmd.Parameters["@HinhAnh"].Value = dto.HinhAnh;
            
            try
            {
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (System.Exception ex)
            {
                return 0;
            }         
        }
        public int CapNhat(SanPhamDTO dto)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "update SAN_PHAM set TenSP = ?, QuyCachSP = ?, GiaSi = ?, GiaLe = ?, MaNSX = ?, MoTa = ?, HinhAnh = ? where MaSP = " + dto.MaSanPham;
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenSP", OleDbType.WChar);
            cmd.Parameters.Add("@QuyCachSP", OleDbType.WChar);
            cmd.Parameters.Add("@GiaSi", OleDbType.Numeric);
            cmd.Parameters.Add("@GiaLe", OleDbType.Numeric);
            cmd.Parameters.Add("@MaNSX", OleDbType.Integer);
            cmd.Parameters.Add("@MoTa", OleDbType.WChar);
            cmd.Parameters.Add("@HinhAnh", OleDbType.WChar);

            cmd.Parameters["@TenSP"].Value = dto.TenSanPham;
            cmd.Parameters["@QuyCachSP"].Value = dto.QuyCach;
            cmd.Parameters["@GiaSi"].Value = dto.DonGiaSy;
            cmd.Parameters["@GiaLe"].Value = dto.DonGiaLe;
            cmd.Parameters["@MaNSX"].Value = dto.MaNSX;
            cmd.Parameters["@MoTa"].Value = dto.MoTa;
            cmd.Parameters["@HinhAnh"].Value = dto.HinhAnh;

            try
            {
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (System.Exception ex)
            {
                return 0;
            }         
        }
        public int Xoa(int masp)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "update SAN_PHAM set TrangThai = -1 where MaSP = "+ masp;
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);           

            try
            {
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (System.Exception ex)
            {
                return 0;
            }         
        }
        public ArrayList layDanhSach()
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select sp.*, TenNSX from SAN_PHAM sp, NHA_SAN_XUAT nsx where sp.MaNSX = nsx.MaNSX AND TrangThai <> -1";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ArrayList list = new ArrayList();
            while (dr.Read())
            {
                SanPhamCRT sp = new SanPhamCRT();
                sp.MaSanPham = (int)dr["MaSP"];
                sp.TenSanPham = (string)dr["TenSP"];
                sp.QuyCach = (string)dr["QuyCachSP"];
                sp.NSX = (string)dr["TenNSX"];
                sp.MoTa = (string)dr["MoTa"];
                //sp.MaNSX = (int)dr["MaNSX"];
                sp.HinhAnh = (string)dr["HinhAnh"];
                sp.DonGiaLe = (double)dr["GiaLe"];
                sp.DonGiaSy = (double)dr["GiaSi"];                
                list.Add(sp);
            }
            dr.Close();
            cn.Close();
            return list;
        }
        public ArrayList layDanhSachTheoQuyCach(string quycach)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select * from SAN_PHAM sp, NHA_SAN_XUAT nsx where sp.MaNSX = nsx.MaNSX AND TrangThai <> -1 AND QuyCachSP = '"+quycach+"'";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ArrayList list = new ArrayList();
            while (dr.Read())
            {
                SanPhamCRT sp = new SanPhamCRT();
                sp.MaSanPham = (int)dr["MaSP"];
                sp.TenSanPham = (string)dr["TenSP"];
                sp.QuyCach = (string)dr["QuyCachSP"];
                sp.NSX = (string)dr["TenNSX"];
                sp.MoTa = (string)dr["MoTa"];
                sp.HinhAnh = (string)dr["HinhAnh"];
                sp.DonGiaLe = (double)dr["GiaLe"];
                sp.DonGiaSy = (double)dr["GiaSi"];
                list.Add(sp);
            }
            dr.Close();
            cn.Close();
            return list;
        }
        public ArrayList layDanhSachTheoNhaSanXuat(string nhasanxuat)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select * from SAN_PHAM sp, NHA_SAN_XUAT nsx where sp.MaNSX = nsx.MaNSX AND TrangThai <> -1 AND TenNSX = '" + nhasanxuat+ "'";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ArrayList list = new ArrayList();
            while (dr.Read())
            {
                SanPhamCRT sp = new SanPhamCRT();
                sp.MaSanPham = (int)dr["MaSP"];
                sp.TenSanPham = (string)dr["TenSP"];
                sp.QuyCach = (string)dr["QuyCachSP"];
                sp.NSX = (string)dr["TenNSX"];
                sp.MoTa = (string)dr["MoTa"];
                sp.HinhAnh = (string)dr["HinhAnh"];
                sp.DonGiaLe = (double)dr["GiaLe"];
                sp.DonGiaSy = (double)dr["GiaSi"];
                list.Add(sp);
            }
            dr.Close();
            cn.Close();
            return list;
        }

        public SanPhamCRT layThongTinSanPham(int masp)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select sp.*, TenNSX from SAN_PHAM sp, NHA_SAN_XUAT nsx where sp.MaNSX = nsx.MaNSX AND TrangThai <> -1 and MaSP="+masp;
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            SanPhamCRT sp = null;
            if (dr.Read())
            {
                sp = new SanPhamCRT();
                sp.MaSanPham = (int)dr["MaSP"];
                sp.TenSanPham = (string)dr["TenSP"];
                sp.QuyCach = (string)dr["QuyCachSP"];
                sp.NSX = (string)dr["TenNSX"];
                sp.MoTa = (string)dr["MoTa"];
                //sp.MaNSX = (int)dr["MaNSX"];
                sp.HinhAnh = (string)dr["HinhAnh"];
                sp.DonGiaLe = (double)dr["GiaLe"];
                sp.DonGiaSy = (double)dr["GiaSi"];
                
            }
            dr.Close();
            cn.Close();
            return sp;
        }
    }
}
